﻿package com.agileai.miscdp.hotweb.database;

import java.io.Reader;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Properties;

import com.agileai.miscdp.DeveloperConst;
import com.agileai.miscdp.hotweb.domain.Column;
import com.agileai.miscdp.hotweb.domain.ProjectConfig;
import com.agileai.miscdp.util.MiscdpUtil;
/**
 * 数据库辅助类
 */
public class DBManager {
	public static class DataBaseType{
		public static final String MySQL = "MySQL";
		public static final String Oracle = "Oracle";
		public static final String SQLServer = "SQLServer";
	}
	
	private static HashMap<String,DBManager> instances = new HashMap<String,DBManager>();
	private DBManager(){
	}
	public static DBManager getDefInstance(){
		return new DBManager();
	}
	private String driverUrl = null;
	private String driverClass = null;
	private String userId = null;
	private String userPwd = null;
	private List<URL> driverJars = new ArrayList<URL>();
	
	public synchronized static DBManager getInstance(String projectname){
		DBManager instance = instances.get(projectname);
		if (instance == null){
			instance = new DBManager();
			instances.put(projectname, instance);
		}
		if (!instance.isInit()){
			ProjectConfig projectConfig = new ProjectConfig();
			String configFile = MiscdpUtil.getCfgFileName(projectname,DeveloperConst.PROJECT_CFG_NAME);
			projectConfig.setConfigFile(configFile);
			projectConfig.initConfig();
			
			instance.setDriverClass(projectConfig.getDriverClass());
			instance.setDriverUrl(projectConfig.getDriverUrl());
			instance.setUserId(projectConfig.getUserId());
			instance.setUserPwd(projectConfig.getUserPwd());
			String databaseType = MiscdpUtil.parseDataBaseType(projectConfig.getDriverUrl());
			String driverPath = MiscdpUtil.getDriverPath(databaseType);
			URL url = MiscdpUtil.toURL(driverPath);
			instance.getDriverJars().add(url);
		}
		return instance;
	}	
	
	public boolean isInit(){
		return this.driverClass != null; 
	}
	public void setDriverUrl(String driverUrl) {
		this.driverUrl = driverUrl;
	}
	public void setDriverClass(String driverClass) {
		this.driverClass = driverClass;
	}
	public void setUserId(String userId) {
		this.userId = userId;
	}
	public void setUserPwd(String userPwd) {
		this.userPwd = userPwd;
	}
	public Connection createConnection(){
		Connection connection = null;
		try {
//			if (this.driverUrl.indexOf("oracle") > -1){
//				Class.forName(this.driverClass);
//				connection = DriverManager.getConnection(this.driverUrl,this.userId,this.userPwd);
//			}
//			else{
				URL[] urls = driverJars.toArray(new URL[0]);
				URLClassLoader classLoader = new URLClassLoader(urls,null);        
				java.sql.Driver driver = (java.sql.Driver) classLoader.loadClass(this.driverClass).newInstance();
				Properties properties = new Properties();
				properties.put("user", this.userId);
				properties.put("password",this.userPwd);
				connection = driver.connect(this.driverUrl, properties);	
//			}
			connection.setAutoCommit(false);
		}
		catch (Exception e) {
		    System.out.println(e.getLocalizedMessage());
		}
		return connection;
	}
	public void releaseConnection(Connection connection) {
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public void release(ResultSet rs,Connection connection) {
		try {
			if (rs != null){
				rs.close();
			}
			if (connection != null) {
				connection.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public void release(ResultSet rs,Statement st,Connection connection) {
		try {
			if (rs != null){
				rs.close();
			}
			if (st != null){
				st.close();
			}
			if (connection != null) {
				connection.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	public void rollbackConnection(Connection connection) {
		try {
			if (connection != null) {
				connection.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List getSchemas(){
		List result = new ArrayList();
		Connection connection = null;
		try {
			connection = this.createConnection();
			ResultSet rs = connection.getMetaData().getSchemas();
			if (rs != null){
				while (rs.next()) {
					String colName = "TABLE_SCHEM";
					Object colValue = rs.getObject(colName);
					result.add(String.valueOf(colValue));
				}
				rs.close();
			}
			connection.commit();
		} catch (SQLException e) {
			rollbackConnection(connection);
			e.printStackTrace();
		}
		finally{
			releaseConnection(connection);
		}
		return result;
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List getTables(String schema){
		List result = new ArrayList();
		Connection connection = null;
		try {
			connection = createConnection();
			String catalog = connection.getCatalog();
			DatabaseMetaData dbmd = connection.getMetaData();
			if (this.driverUrl.indexOf("oracle") > -1 
					&& !"sys".equals(this.userId.toLowerCase())
					&& !"system".equals(this.userId.toLowerCase())){
				schema = this.userId.toUpperCase();	
			}
			
			ResultSet rs = dbmd.getTables(catalog, schema, null, new String[]{"TABLE"});
			if (rs != null){
				while (rs.next()) {
					String colName = "TABLE_NAME";
					Object colValue = rs.getObject(colName);
					result.add(String.valueOf(colValue));
				}
				rs.close();
			}
			connection.commit();
		} catch (SQLException e) {
			rollbackConnection(connection);
			e.printStackTrace();
		}
		finally{
			releaseConnection(connection);
		}
		return result;
	}
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List getViews(String schema){
		List result = new ArrayList();
		Connection connection = null;
		try {
			connection = createConnection();
			ResultSet rs = connection.getMetaData().getTables(null, schema, null, new String[]{"VIEW"});
			if (rs != null){
				while (rs.next()) {
					String colName = "TABLE_NAME";
					Object colValue = rs.getObject(colName);
					result.add(String.valueOf(colValue));
				}
				rs.close();
			}
			connection.commit();
		} catch (SQLException e) {
			rollbackConnection(connection);
			e.printStackTrace();
		}
		finally{
			releaseConnection(connection);
		}
		return result;
	}
	public ResultSet getResultSet(Connection connection,String sql) {
		ResultSet result = null;
		try {
			result = connection.createStatement().executeQuery(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return result;
	}
	public int executeUpdateSql(String sql){
		int result = 0;
		Connection connection = null;
		try {
			connection = createConnection();
			result = connection.createStatement().executeUpdate(sql);
			connection.commit();
		} catch (SQLException e) {
			rollbackConnection(connection);
			e.printStackTrace();
		}
		finally{
			releaseConnection(connection);
		}
		return result;
	}
	@SuppressWarnings({"rawtypes" })
	public int getNextId(String tableName,String IdColumnName){
		int result = 0;
		String sql = "SELECT max("+IdColumnName+") as MAX_ID FROM " + tableName;
		HashMap row = this.getRecord(sql);
		if (row != null && row.size() > 0){
			String maxId = String.valueOf(row.get("MAX_ID"));
			if (!"null".equals(maxId)){
				result = Integer.parseInt(maxId); 
			}
		}
		result = result+1;
		return result;
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public List<HashMap> getRecords(String sql){
		List<HashMap> result = new ArrayList<HashMap>();
		Connection connection = null;
		try {
			connection = createConnection();
			ResultSet rs = this.getResultSet(connection,sql);
			if (rs != null){
				ResultSetMetaData metaData = rs.getMetaData();
				int count = metaData.getColumnCount();
				while (rs.next()) {
					HashMap row = new HashMap();
					result.add(row);
					for (int i=1;i <= count;i++){
						String colName = metaData.getColumnName(i);
						Object colValue = rs.getObject(colName);
						if (colValue != null){
							row.put(colName,colValue);
						}
					}
				}
			}
			connection.commit();
		} catch (Exception e) {
			rollbackConnection(connection);
			e.printStackTrace();
		}
		finally{
			releaseConnection(connection);			
		}
		return result;
	}
	@SuppressWarnings({ "unchecked", "rawtypes" })
	public HashMap getRecord(String sql){
		HashMap result = new HashMap();
		Connection connection = null;
		try {
			connection = createConnection();
			ResultSet rs = getResultSet(connection,sql);
			if (rs != null){
				rs.next();
				ResultSetMetaData metaData = rs.getMetaData();
				int count = metaData.getColumnCount();
				HashMap row = new HashMap();
				for (int i=1;i <= count;i++){
					String colName = metaData.getColumnName(i);
					Object colValue = rs.getObject(colName);
					if (colValue != null){
						row.put(colName,colValue);
					}
				}
				result = row;
			}
			connection.commit();
		} catch (Exception e) {
			rollbackConnection(connection);
			e.printStackTrace();
		}
		finally{
			releaseConnection(connection);			
		}
		return result;
	}
	
	public String getClobValue(String clobName,String sql){
		String result = null;
		Connection connection = null;
		try {
			connection = createConnection();
			ResultSet rs = getResultSet(connection,sql);
			if (rs.next()) {
				java.sql.Clob clob = rs.getClob(clobName);
				Reader inStream = clob.getCharacterStream();
				char[] c = new char[(int) clob.length()];
				inStream.read(c);
				result = new String(c);
				inStream.close();
			}
			connection.commit();
		} catch (Exception e) {
			rollbackConnection(connection);
			e.printStackTrace();
		}
		finally{
			releaseConnection(connection);			
		}
		return result;
	}
	
	private String metaDataSql(String tableName){
		StringBuffer sql = new StringBuffer();
		sql.append("select * from ").append(tableName).append(" where 1=2");
		return sql.toString();
	}

	public HashMap<String, Column> getColumnMap(String tableName) {
		HashMap<String, Column> result = new HashMap<String, Column>();
		Connection connection = null;
		try {
			connection = createConnection();
			ResultSet rs = this.getResultSet(connection,this.metaDataSql(tableName));
			ResultSetMetaData rsMetaData = rs.getMetaData();
			DatabaseMetaData databaseMetaData = connection.getMetaData();
			String driverName = databaseMetaData.getDriverName();
			String databaseType = this.getDataBaseType(driverName);
//			String databaseProductName = databaseMetaData.getDatabaseProductName();
//			System.out.println(databaseProductName);
			int colCount = rsMetaData.getColumnCount();
			Column[] columns = new Column[colCount];
			for (int i = 1; i <= colCount; i++) {
				columns[i-1] = new Column();
				String colName = rsMetaData.getColumnName(i);
				String colLabel = rsMetaData.getColumnLabel(i);
				String colTypeName = rsMetaData.getColumnTypeName(i);
				int jdbcType = rsMetaData.getColumnType(i);
				
				String colClassName = rsMetaData.getColumnClassName(i);
				int nullable = rsMetaData.isNullable(i);
				int displaySize = rsMetaData.getColumnDisplaySize(i);
				columns[i-1].setName(colName);
				columns[i-1].setLabel(colLabel);
				columns[i-1].setTypeName(colTypeName);
				columns[i-1].setJdbcType(jdbcType);
				columns[i-1].setClassName(colClassName);
				this.computeJdbcTypeName(columns[i-1], databaseType, colTypeName,colClassName);
				
				if (0 == nullable) {
					columns[i-1].setNullable(false);
				} else {
					columns[i-1].setNullable(true);
				}
				columns[i-1].setLength(displaySize);
				result.put(colName,columns[i-1]);
			}
			String catalog = connection.getCatalog();
			rs = connection.getMetaData().getPrimaryKeys(catalog, null,tableName);
			while (rs.next()) {
				String pkColName = rs.getString("COLUMN_NAME");
				setPKColumn(columns, pkColName);
			}
			connection.commit();
		} catch (Exception e) {
			rollbackConnection(connection);
			e.printStackTrace();
		} finally {
			releaseConnection(connection);
		}
		return result;
	}
	
	private void computeJdbcTypeName(Column column,String databaseType,String colTypeName,String javaType){
//		if (databaseType.equals(DataBaseType.Oracle)){
//			if (colTypeName.equals("NUMBER") && "java.lang.long".equals(javaType)){
//				column.setJdbcTypeName("BIGINT");
//			}
//			else if (colTypeName.equals("RAW") && "byte[]".equals(javaType)){
//				column.setJdbcTypeName("BINARY");
//			}
//			else if (colTypeName.equals("BIT") && "java.lang.Boolean".equals(javaType)){
//				column.setJdbcTypeName("BIT");
//			}
//			else if (colTypeName.equals("BLOB") && "byte[]".equals(javaType)){
//				column.setJdbcTypeName("BLOB");
//			}
//			else if ((colTypeName.equals("CHAR") || colTypeName.equals("NCHAR"))
//					&& "java.lang.String".equals(javaType)){
//				column.setJdbcTypeName("CHAR");
//			}
//			else if (colTypeName.equals("CLOB") && "java.lang.String".equals(javaType)){
//				column.setJdbcTypeName("CLOB");
//			}
//			else if (colTypeName.equals("DATE") && "java.sql.Date".equals(javaType)){
//				column.setJdbcTypeName("DATE");
//			}
//			else if (colTypeName.equals("NUMBER") && "java.math.BigDecimal".equals(javaType)){
//				column.setJdbcTypeName("DECIMAL");
//			}
//			else if (colTypeName.equals("DOUBLE PRECISION") && "java.lang.Double".equals(javaType)){
//				column.setJdbcTypeName("DOUBLE");
//			}
//			else if (colTypeName.equals("FLOAT") && "java.lang.Double".equals(javaType)){
//				column.setJdbcTypeName("FLOAT");
//			}
//			else if (colTypeName.equals("INTEGER") && "java.lang.Integer".equals(javaType)){
//				column.setJdbcTypeName("INTEGER");
//			}
//			else if (colTypeName.equals("JAVA_OBJECT") && "java.lang.Object".equals(javaType)){
//				column.setJdbcTypeName("JAVA_OBJECT");
//			}
//			else if (colTypeName.equals("LONG RAW") && "byte[]".equals(javaType)){
//				column.setJdbcTypeName("LONGVARBINARY");
//			}
//			else if (colTypeName.equals("LONG") && "java.lang.String".equals(javaType)){
//				column.setJdbcTypeName("LONGVARCHAR");
//			}
//			else if (colTypeName.equals("NUMBER") && "java.math.BigDecimal".equals(javaType)){
//				column.setJdbcTypeName("NUMERIC");
//			}
//			else if (colTypeName.equals("OTHER") && "java.lang.Object".equals(javaType)){
//				column.setJdbcTypeName("OTHER");
//			}
//			else if (colTypeName.equals("REAL") && "java.lang.Float".equals(javaType)){
//				column.setJdbcTypeName("REAL");
//			}
//			else if (colTypeName.equals("SMALLINT") && "java.lang.Integer".equals(javaType)){
//				column.setJdbcTypeName("SMALLINT");
//			}			
//			else if (colTypeName.equals("DATE") && "java.sql.Time".equals(javaType)){
//				column.setJdbcTypeName("TIME");
//			}		
//			else if (colTypeName.equals("DATE") && "java.sql.Timestamp".equals(javaType)){
//				column.setJdbcTypeName("TIMESTAMP");
//			}		
//			else if (colTypeName.equals("TINYINT") && "java.lang.Byte".equals(javaType)){
//				column.setJdbcTypeName("TINYINT");
//			}		
//			else if (colTypeName.equals("RAW") && "byte[]".equals(javaType)){
//				column.setJdbcTypeName("VARBINARY");
//			}		
//			else if ((colTypeName.equals("VARCHAR") || colTypeName.equals("VARCHAR2") || colTypeName.equals("NVARCHAR"))  
//					&& "java.lang.String".equals(javaType)){
//				column.setJdbcTypeName("VARCHAR");
//			}		
//		}
//		else if (databaseType.equals(DataBaseType.SQLServer)){
			if (Types.ARRAY == column.getJdbcType()){
				column.setJdbcTypeName("ARRAY");
			}
			else if (Types.BINARY == column.getJdbcType()){
				column.setJdbcTypeName("BINARY");
			}
			else if (Types.BIT == column.getJdbcType()){
				column.setJdbcTypeName("BIT");
			}
			else if (Types.BLOB == column.getJdbcType()){
				column.setJdbcTypeName("BLOB");
			}
			else if (Types.BOOLEAN == column.getJdbcType()){
				column.setJdbcTypeName("BOOLEAN");
			}
			else if (Types.CHAR == column.getJdbcType()){
				column.setJdbcTypeName("CHAR");
			}
			else if (Types.CLOB == column.getJdbcType()){
				column.setJdbcTypeName("CLOB");
			}
			else if (Types.DATALINK == column.getJdbcType()){
				column.setJdbcTypeName("DATALINK");
			}
			else if (Types.DATE == column.getJdbcType()){
				column.setJdbcTypeName("DATE");
			}
			else if (Types.DECIMAL == column.getJdbcType()){
				column.setJdbcTypeName("DECIMAL");
			}
			else if (Types.DISTINCT == column.getJdbcType()){
				column.setJdbcTypeName("DISTINCT");
			}
			else if (Types.DOUBLE == column.getJdbcType()){
				column.setJdbcTypeName("DOUBLE");
			}			
			else if (Types.FLOAT == column.getJdbcType()){
				column.setJdbcTypeName("FLOAT");
			}
			else if (Types.INTEGER == column.getJdbcType()){
				column.setJdbcTypeName("INTEGER");
			}
			else if (Types.JAVA_OBJECT == column.getJdbcType()){
				column.setJdbcTypeName("JAVA_OBJECT");
			}			
			else if (Types.LONGNVARCHAR == column.getJdbcType()){
				column.setJdbcTypeName("LONGNVARCHAR");
			}
			else if (Types.LONGVARBINARY == column.getJdbcType()){
				column.setJdbcTypeName("LONGVARBINARY");
			}
			else if (Types.LONGVARCHAR == column.getJdbcType()){
				column.setJdbcTypeName("LONGVARCHAR");
			}
			else if (Types.NCHAR == column.getJdbcType()){
				column.setJdbcTypeName("NCHAR");
			}
			else if (Types.NCLOB == column.getJdbcType()){
				column.setJdbcTypeName("NCLOB");
			}
			else if (Types.NUMERIC == column.getJdbcType()){
				column.setJdbcTypeName("NUMERIC");
			}
			else if (Types.NVARCHAR == column.getJdbcType()){
				column.setJdbcTypeName("NVARCHAR");
			}
			else if (Types.SMALLINT == column.getJdbcType()){
				column.setJdbcTypeName("SMALLINT");
			}
			else if (Types.OTHER == column.getJdbcType()){
				column.setJdbcTypeName("OTHER");
			}
			else if (Types.REAL == column.getJdbcType()){
				column.setJdbcTypeName("REAL");
			}	
			else if (Types.REF == column.getJdbcType()){
				column.setJdbcTypeName("REF");
			}	
			else if (Types.SMALLINT == column.getJdbcType()){
				column.setJdbcTypeName("SMALLINT");
			}	
			else if (Types.SQLXML == column.getJdbcType()){
				column.setJdbcTypeName("SQLXML");
			}	
			else if (Types.STRUCT == column.getJdbcType()){
				column.setJdbcTypeName("STRUCT");
			}
			else if (Types.TIME == column.getJdbcType()){
				column.setJdbcTypeName("TIME");
			}
			else if (Types.TIMESTAMP == column.getJdbcType()){
				column.setJdbcTypeName("TIMESTAMP");
			}
			else if (Types.TINYINT == column.getJdbcType()){
				column.setJdbcTypeName("TINYINT");
			}
			else if (Types.VARBINARY == column.getJdbcType()){
				column.setJdbcTypeName("VARBINARY");
			}
			else if (Types.VARCHAR == column.getJdbcType()){
				column.setJdbcTypeName("VARCHAR");
			}
//		}
	}
	
    public Column[] getColumns(String tableName){
    	Column[] result = null;
    	Connection connection = null;
		try {
			connection = createConnection();
	    	ResultSet rs = this.getResultSet(connection,this.metaDataSql(tableName));
	    	ResultSetMetaData rsMetaData = rs.getMetaData();
			int colCount = rsMetaData.getColumnCount();
			result = new Column[colCount];
			for (int i=1;i <= colCount;i++){
				result[i-1] = new Column();
				String colName = rsMetaData.getColumnName(i);
				String colLabel = rsMetaData.getColumnLabel(i);
				String colTypeName = rsMetaData.getColumnTypeName(i);
				int jdbcType = rsMetaData.getColumnType(i);
				String colClassName = rsMetaData.getColumnClassName(i);
				int nullable = rsMetaData.isNullable(i);
				int displaySize = rsMetaData.getColumnDisplaySize(i);

				result[i-1].setName(colName);
				result[i-1].setLabel(colLabel);
				result[i-1].setTypeName(colTypeName);
				result[i-1].setJdbcType(jdbcType);
				result[i-1].setClassName(colClassName);
				
				if (0 == nullable){
					result[i-1].setNullable(false);
				}else{
					result[i-1].setNullable(true);
				}
				result[i-1].setLength(displaySize);
			}
			String catalog = connection.getCatalog();
			rs = connection.getMetaData().getPrimaryKeys(catalog,null, tableName);
			while(rs.next()){
				String pkColName = rs.getString("COLUMN_NAME");
				setPKColumn(result,pkColName);
			}
			connection.commit();
		} catch (Exception e) {
			rollbackConnection(connection);
			e.printStackTrace();
		}
		finally{
			releaseConnection(connection);			
		}
    	return result;
    }
    private void setPKColumn(Column[] columns,String pkColumn){
    	for (int i=0;i < columns.length;i++){
    		Column column = columns[i];
    		if (column.getName().equals(pkColumn)){
    			column.setPK(true);
    			break;
    		}
    	}
    }
    @SuppressWarnings({ "unchecked", "rawtypes" })
	public String[] getPrimaryKeys(String tableName){
    	String[] result = null;
    	Connection connection = null;
		try {
			connection = createConnection();
	    	DatabaseMetaData databaseMetaData = connection.getMetaData();
	    	String catalog = connection.getCatalog();
	    	ResultSet rs = databaseMetaData.getPrimaryKeys(catalog, null, tableName);
	    	List list = new ArrayList();
			while(rs.next()){
				String colName = rs.getString("COLUMN_NAME");
				list.add(colName);
			}
			if (!list.isEmpty()){
				result = (String[])list.toArray(new String[0]);				
			}
			rs.close();
		connection.commit();
		} catch (Exception e) {
			rollbackConnection(connection);
			e.printStackTrace();
		}
		finally{
			releaseConnection(connection);			
		}
    	return result;
    }
	public List<URL> getDriverJars() {
		return driverJars;
	}
	public String getDriverUrl() {
		return driverUrl;
	}
	
	public String getDataBaseType(String driverName){
		String result = DataBaseType.MySQL;
		if (driverName.toLowerCase().indexOf("oracle") > -1){
			result = DataBaseType.Oracle;
		}
		return result;
	}
}
